#ifndef esda_SQLiteStream_h
#define esda_SQLiteStream_h

/*
 * Copyright (C) Olivier Le Floch
 *
 * See the file LICENSE for information on usage and redistribution of
 * this file, and for a DISCLAIMER OF ALL WARRANTIES.
 */

#include "common.h"
#include "Stream.h"
#include "../libs/sqlite3.h"
#include "SQLite.h"

#define STREAM_TABLE_STRUCTURE_STRING "(\
	Id        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
	StartTime INTEGER NOT NULL UNIQUE, \
	EndTime   INTEGER NOT NULL UNIQUE, \
	Value     TEXT NULL\
	)"

#define STREAM_TABLE_STRUCTURE_INT "(\
	Id        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
	StartTime INTEGER NOT NULL UNIQUE, \
	EndTime   INTEGER NOT NULL UNIQUE, \
	Value     INTEGER NULL\
	)"

#define STREAM_TABLE_STRUCTURE_DOUBLE "(\
	Id        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
	StartTime INTEGER NOT NULL UNIQUE, \
	EndTime   INTEGER NOT NULL UNIQUE, \
	Value     REAL NULL\
	)"

namespace esda
{
	template<typename Value>
	class SQLiteStream : public DataStream<Value> {
	private:
		sqlite3 *database;
		const string &streamName;
		bool connected;
		
		bool createDatabase(const string& databaseName, const string& streamName, const string& schema);
		
		void setValue(const Timestamp& startTime, const Timestamp& endTime, const Value v);
		
		void runSetValueQuery(const Timestamp& startTime, const Timestamp& endTime, string& query);
	
	protected:
		void runCreateQuery(const string& query);
	
	public:
		SQLiteStream(const string& databaseName, const string& streamName, const bool createTable = true);
		SQLiteStream(const SQLiteStream& SourceStream)
			: DataStream<Value>(), database(), streamName(streamName), connected(false) {};
		
		~SQLiteStream();
		
		virtual Value get(const Timestamp t) const;
		
		virtual Timestamp getNextStart(const Timestamp t) const;
		virtual Timestamp getNextEnd(const Timestamp t) const;
		virtual Timestamp getPreviousStart(const Timestamp t) const;
		virtual Timestamp getPreviousEnd(const Timestamp t) const;
		
		virtual Timestamp getFirstStart() const;
		virtual Timestamp getLastEnd() const;
		
		virtual void set(const Timestamp& startAndEndTime, const Value v);
		virtual void set(const Timestamp& startTime, const Timestamp& endTime, const Value v);
		
		SQLiteStream &operator=(const SQLiteStream& SourceStream) {
			database   = SourceStream.database;
			streamName = SourceStream.streamName;
			connected  = SourceStream.connected;
			
			return *this;
		};
	};
	
	template<typename Value>
	SQLiteStream<Value>::SQLiteStream(const string& databaseName, const string& streamName2, const bool createTable)
		: database(NULL), streamName(streamName2), connected(false)
	{
		connected = SQLiteStream<Value>::createDatabase(databaseName, streamName2, createTable ? STREAM_TABLE_STRUCTURE_STRING : "");
	}
	
	template<>
	SQLiteStream<double>::SQLiteStream(const string& databaseName, const string& streamName2, const bool createTable)
		: database(NULL), streamName(streamName2), connected(false)
	{
		connected = SQLiteStream<double>::createDatabase(databaseName, streamName2, createTable ? STREAM_TABLE_STRUCTURE_DOUBLE : "");
	}
	
	template<>
	SQLiteStream<int>::SQLiteStream(const string& databaseName, const string& streamName2, const bool createTable)
		: database(NULL), streamName(streamName2), connected(false)
	{
		connected = SQLiteStream<int>::createDatabase(databaseName, streamName2, createTable ? STREAM_TABLE_STRUCTURE_INT : "");
	}
	
	template<typename Value>
	bool SQLiteStream<Value>::createDatabase(const string& databaseName, const string& streamName2, const string& schema) {
		int connexion;
		
		connexion = sqlite3_open(databaseName.c_str(), &database);
	
		if (connexion) {
			ERROR("Failed to open database -- " << databaseName << " -- " << sqlite3_errmsg(database));
		} else {
			connected = true;
		}
		
		// Si le schema fourni est vide, c'est qu'en fait on ne souhaite pas créer la table
		
		if (schema.length()) {
			runCreateQuery("CREATE TABLE IF NOT EXISTS `" + streamName2 + "` " + schema);
		}
		
		return true;
	}
	
	template<typename Value>
	void SQLiteStream<Value>::runCreateQuery(const string& query) {
		sqlite3_stmt *stmt = 0;
		
		if (sqlite3_prepare_v2(database, query.c_str(), -1, &stmt, NULL)) {
			SQLite_ERROR("Create query", query);
		}
		
		int resultat = sqlite3_step(stmt);
		
		if (resultat != SQLITE_DONE) {
			SQLite_ERROR("Run query", query);
		}
		
		if (sqlite3_finalize(stmt)) {
			SQLite_ERROR("Finalize query", query);
		}
	}
	
	template<typename Value>
	SQLiteStream<Value>::~SQLiteStream() {
		if (connected) {
			if (sqlite3_close(database) != SQLITE_OK) {
				ERROR("Failed to close database connexion.");
			}
			
			connected = false;
		}
	}
	
	template<typename Value>
	Value SQLiteStream<Value>::get(const Timestamp t) const {
		return NULL;
	}
	
	template<>
	string SQLiteStream<string>::get(const Timestamp t) const {
		sqlite3_stmt *stmt = 0;
		string query =
			"SELECT Value FROM " + streamName + " WHERE StartTime <= :Time AND EndTime >= :Time";
	
		if (sqlite3_prepare_v2(database, query.c_str(), -1, &stmt, NULL)) {
			SQLite_ERROR("Create query", query);
		}
		
		if (sqlite3_bind_int64(stmt, 1, t.toInt()) != SQLITE_OK) {
			SQLite_ERROR("Bind parameter Time -- " << t << " -- ", query);
		}
		
		int result = sqlite3_step(stmt);
		string v;
		
		if (result != SQLITE_ROW) {
			if (result != SQLITE_DONE) {
				SQLite_ERROR("Run query (sqlite3_step returned " << result << ")", query);
			} else {
				v = (string) NULL;
			}
		} else {
			v = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
		}
		
	
		if (sqlite3_finalize(stmt)) {
			SQLite_ERROR("Finalize query", query);
		}
		
		return v;
	}
	
	template<>
	double SQLiteStream<double>::get(const Timestamp t) const {
		sqlite3_stmt *stmt = 0;
		string query =
			"SELECT Value FROM " + streamName + " WHERE StartTime <= :Time AND EndTime >= :Time";
	
		if (sqlite3_prepare_v2(database, query.c_str(), -1, &stmt, NULL)) {
			SQLite_ERROR("Create query", query);
		}
		
		if (sqlite3_bind_int64(stmt, 1, t.toInt()) != SQLITE_OK) {
			SQLite_ERROR("Bind parameter Time -- " << t << " -- ", query);
		}
		
		int result = sqlite3_step(stmt);
		double v;
		
		if (result != SQLITE_ROW) {
			if (result != SQLITE_DONE) {
				SQLite_ERROR("Run query (sqlite3_step returned " << result << ")", query);
			} else {
				v = NULL;
			}
		} else {
			v = sqlite3_column_double(stmt, 0);
		}
		
		if (sqlite3_finalize(stmt)) {
			SQLite_ERROR("Finalize query", query);
		}
		
		return v;
	}
	
	template<typename Value>
	Timestamp SQLiteStream<Value>::getNextStart(const Timestamp startTime) const {
		return SQLite::fetchTimestamp(
			database,
			"SELECT MIN(StartTime) FROM `" + streamName + "` WHERE StartTime >= " + startTime);
	}
	
	template<typename Value>
	Timestamp SQLiteStream<Value>::getNextEnd(const Timestamp endTime) const {
		return SQLite::fetchTimestamp(
			database,
			"SELECT MIN(EndTime) FROM `" + streamName + "` WHERE EndTime >= " + endTime);
	}
	
	template<typename Value>
	Timestamp SQLiteStream<Value>::getPreviousStart(const Timestamp startTime) const {
		return SQLite::fetchTimestamp(
			database,
			"SELECT MAX(StartTime) FROM `" + streamName + "` WHERE StartTime <= " + startTime);
	}
	
	template<typename Value>
	Timestamp SQLiteStream<Value>::getPreviousEnd(const Timestamp endTime) const {
		return SQLite::fetchTimestamp(
			database,
			"SELECT MAX(EndTime) FROM `" + streamName + "` WHERE EndTime <= " + endTime);
	}
	
	template<typename Value>
	Timestamp SQLiteStream<Value>::getFirstStart() const {
		return SQLite::fetchTimestamp(
			database,
			"SELECT MIN(StartTime) FROM `" + streamName + "`");
	}

	template<typename Value>
	Timestamp SQLiteStream<Value>::getLastEnd() const {
		return SQLite::fetchTimestamp(
			database,
			"SELECT MAX(EndTime) FROM `" + streamName + "`");
	}
	
	template<typename Value>
	void SQLiteStream<Value>::set(const Timestamp& startAndEndTime, const Value v) {
		set(startAndEndTime, startAndEndTime, v);
	}
	
	template<typename Value>
	void SQLiteStream<Value>::set(const Timestamp& startTime, const Timestamp& endTime, const Value v) {
		Timestamp
			firstTimeAfterStartTime = getNextStart(startTime),
			lastTimeBeforeEndTime   = getPreviousEnd(endTime);
		
		if (firstTimeAfterStartTime <= endTime) {
			SQLite::runQuery(
				database,
				"UPDATE `" + streamName + "` SET StartTime = " + endTime + " + 1 WHERE StartTime = " + firstTimeAfterStartTime);
		}
		
		if (lastTimeBeforeEndTime >= startTime) {
			SQLite::runQuery(
				database,
				"UPDATE `" + streamName + "` SET EndTime = " + startTime + " - 1 WHERE EndTime = " + lastTimeBeforeEndTime);
		}
		
		setValue(startTime, endTime, v);
	}
	
	template<typename Value>
	void SQLiteStream<Value>::setValue(const Timestamp& startTime, const Timestamp& endTime, const Value v) {
		runSetValueQuery(
			startTime, endTime,
			"INSERT INTO " + streamName + " (StartTime, EndTime, Value) VALUES(?, ?, '" + v + "')");
	}
	
	template<typename Value>
	void SQLiteStream<Value>::runSetValueQuery(const Timestamp& startTime, const Timestamp& endTime, string& query) {
		sqlite3_stmt *stmt = 0;
		
		if (sqlite3_prepare_v2(database, query.c_str(), -1, &stmt, NULL)) {
			SQLite_ERROR("Create query", query);
		}
		
		if (sqlite3_bind_int64(stmt, 1, startTime.toInt()) != SQLITE_OK) {
			SQLite_ERROR("Bind parameter StartTime -- " << startTime << " -- ", query);
		}
		
		if (sqlite3_bind_int64(stmt, 2, endTime.toInt()) != SQLITE_OK) {
			SQLite_ERROR("Bind parameter EndTime -- " << endTime << " -- ", query);
		}
		
		if (sqlite3_step(stmt) != SQLITE_DONE) {
			SQLite_ERROR("Run query", query);
		}
		
		if (sqlite3_finalize(stmt)) {
			SQLite_ERROR("Finalize query", query);
		}
	}
	
	template<>
	void SQLiteStream<string>::setValue(const Timestamp& startTime, const Timestamp& endTime, const string v) {
		string query = "INSERT INTO " + streamName + " (StartTime, EndTime, Value) VALUES(?, ?, '" + v + "')";
		
		runSetValueQuery(startTime, endTime, query);
	}
	
	template<>
	void SQLiteStream<double>::set(const Timestamp &startTime, const Timestamp& endTime, const double v) {
		string query = "INSERT INTO " + streamName + " (StartTime, EndTime, Value) VALUES(?, ?, '" + v + "')";
		
		runSetValueQuery(startTime, endTime, query);
	}
	
	template<>
	void SQLiteStream<int>::set(const Timestamp &startTime, const Timestamp& endTime, const int v) {
		string query = "INSERT INTO " + streamName + " (StartTime, EndTime, Value) VALUES(?, ?, '" + v + "')";
		
		runSetValueQuery(startTime, endTime, query);
	}
}

#endif
